package com.aizhixin.lab.course.course.repository;

import com.aizhixin.lab.course.course.domain.CourseStuInfoDomain;
import com.aizhixin.lab.course.course.domain.CourseStuListDomain;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class CourseJdbc {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<CourseStuInfoDomain> getCourseStuInfoList(String courseId, Long stuId) {
        String sql = "SELECT tu.id,tu.`name`,tu.AVATAR FROM t_user tu INNER JOIN t_course_class_stu tccs ON tccs.stu_id=tu.id INNER JOIN t_course_class tcc ON tcc.id=tccs.course_class_id WHERE tu.delete_flag=0 AND tccs.delete_flag=0 AND tcc.delete_flag=0 AND tcc.course_id='" + courseId + "'";
        RowMapper<CourseStuInfoDomain> rowMapper = new RowMapper<CourseStuInfoDomain>() {
            @Override
            public CourseStuInfoDomain mapRow(ResultSet rs, int rowNum) throws SQLException {
                CourseStuInfoDomain ad = new CourseStuInfoDomain();
                if (rs.getInt("id") != stuId.intValue()) {
                    ad.setName(rs.getString("name"));
                    ad.setAvatar(rs.getString("AVATAR"));
                }
                return ad;
            }
        };
        return jdbcTemplate.query(sql, rowMapper);
    }

    public List<CourseStuListDomain> getStudentCourseList(Pageable pageable, Long stuId, String name) {
        String sql = "SELECT cc.id,cc.`name`,cc.type,cc.class_hour,cc.view_count_chapter,cc.view_count_section,cc.image,tu.`name` teachername,tu.AVATAR FROM c_course cc LEFT JOIN t_user tu ON tu.id=cc.created_by INNER JOIN t_course_class tcc ON tcc.course_id=cc.id INNER JOIN t_course_class_stu tccs ON tccs.course_class_id=tcc.id";
        Map<String, Object> whereMap = getWhereSql(stuId, name);
        String whereSql = (String) whereMap.get("whereSql");
        whereSql += " ORDER BY cc.created_date DESC";
        List<Object> params = (List<Object>) whereMap.get("params");
        if (!StringUtils.isEmpty(whereSql)) {
            sql += whereSql;
        }
        sql += " LIMIT " + pageable.getOffset() + ", " + pageable.getPageSize();
        RowMapper<CourseStuListDomain> rowMapper = new RowMapper<CourseStuListDomain>() {
            @Override
            public CourseStuListDomain mapRow(ResultSet rs, int rowNum) throws SQLException {
                CourseStuListDomain ad = new CourseStuListDomain();
                ad.setId(rs.getString("id"));
                if (!StringUtils.isEmpty(rs.getString("name"))) {
                    ad.setName(rs.getString("name"));
                }
                if (!StringUtils.isEmpty(rs.getInt("type"))) {
                    ad.setType(rs.getInt("type"));
                }
                if (!StringUtils.isEmpty(rs.getInt("class_hour"))) {
                    ad.setClassHour(rs.getInt("class_hour"));
                }
                if (!StringUtils.isEmpty(rs.getInt("view_count_chapter"))) {
                    ad.setCountChapter(rs.getInt("view_count_chapter"));
                }
                if (!StringUtils.isEmpty(rs.getInt("view_count_section"))) {
                    ad.setCountSection(rs.getInt("view_count_section"));
                }
                if (!StringUtils.isEmpty(rs.getString("image"))) {
                    ad.setImage(rs.getString("image"));
                }
                if (!StringUtils.isEmpty(rs.getString("teachername"))) {
                    ad.setTeacherName(rs.getString("teachername"));
                }
                if (!StringUtils.isEmpty(rs.getString("AVATAR"))) {
                    ad.setTeacherAvatar(rs.getString("AVATAR"));
                }
                return ad;
            }
        };
        return jdbcTemplate.query(sql, params.toArray(), rowMapper);
    }

    public Long countStudentCourseList(Long stuId, String name) {
        String sql = "SELECT COUNT(*) FROM c_course cc LEFT JOIN t_user tu ON tu.id=cc.created_by INNER JOIN t_course_class tcc ON tcc.course_id=cc.id INNER JOIN t_course_class_stu tccs ON tccs.course_class_id=tcc.id";
        Map<String, Object> whereMap = getWhereSql(stuId, name);
        String whereSql = (String) whereMap.get("whereSql");
        List<Object> params = (List<Object>) whereMap.get("params");
        if (!StringUtils.isEmpty(whereSql)) {
            sql += whereSql;
        }
        return jdbcTemplate.queryForObject(sql, params.toArray(), Long.class);
    }

    private Map<String, Object> getWhereSql(Long stuId, String name) {
        String whereSql = " WHERE cc.delete_flag=0 AND cc.is_publish=1 AND cc.is_archive=0 AND tcc.delete_flag=0 AND tccs.delete_flag=0";
        List<Object> params = new ArrayList<>();
        if (stuId != null && stuId.longValue() > 0) {
            whereSql += " AND tccs.stu_id=?";
            params.add(stuId);
        }
        if (!StringUtils.isEmpty(name)) {
            whereSql += " AND LOWER(cc.name) like ?";
            params.add("%" + name.toLowerCase() + "%");
        }
        Map<String, Object> result = new HashMap<>();
        result.put("whereSql", whereSql);
        result.put("params", params);
        return result;
    }
}
